package com.windsea.relationshipmining.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBUserData {

	// DEFINE KEY
	public static final String KEY_ROWID = "_id";
	public static final String KEY_DIFF_AGE = "DiffAge";
	public static final String KEY_AGE = "Age";
	public static final String KEY_GENDER = "Gender";
	public static final String KEY_SEEN = "Seen";

	public static final String KEY_CONTACTID = "ContactID";
	public static final String KEY_NAME = "Name";
	public static final String KEY_FAMILY = "Family";
	public static final String KEY_WORK = "Work";
	public static final String KEY_ACQUAINTANCE = "Acquaintance";
	public static final String KEY_FRIEND = "Friend";
	public static final String KEY_OTHER = "Other";
	public static final String KEY_PHONE = "Phonenumber";
	public static final String KEY_PHONETYPE = "Phonetype";
	public static final String KEY_EMAIL = "Emailaddress";
	public static final String KEY_EMAILTYPE = "Emailtype";
	public static final String KEY_ADDRESS = "Address";
	public static final String KEY_ADDRESSTYPE = "Addresstype";
	public static final String KEY_COMPANY = "CompanyName";
	public static final String KEY_TITLE = "Occupationtitle";
	public static final String KEY_NOTE = "Note";
	public static final String KEY_IM = "IM";
	public static final String KEY_IMTYPE = "IMType";
	public static final String KEY_STARED = "IsStared";
	public static final String KEY_PHOTO = "HasPhoto";
	public static final String KEY_ANNIVERSARY = "HasAnniversary";
	public static final String KEY_NUMBEROFCALL = "NumOfCall";
	public static final String KEY_NUMBEROFSMS = "NumOfSMS";
	public static final String KEY_FBFRIEND = "FacebookFriend";
	public static final String KEY_JOB = "Job";
	public static final String KEY_IMEI = "Imei";
	public static final String KEY_CALLID = "idCall";
	public static final String KEY_PHONENUMBER = "Phonenumber";
	public static final String KEY_DURATION = "Duration";
	public static final String KEY_CALLTYPE = "CallType";
	public static final String KEY_SMSID = "SMSID";
	public static final String KEY_DATETIME = "DateTime";
	public static final String KEY_THREADID = "ThreadID";
	public static final String KEY_SMSLENGTH = "SMSLength";
	public static final String KEY_TYPE = "SMSType";

	// DEFINE TAG
	private static final String TAG = "DBUserData";

	// DEFINE DATABASE NAME
	private static final String DATABASE_NAME = "userdata";

	// DEFINE TABLE NAME
	private static final String DATABASE_USERINFO = "userinfo";
	private static final String DATABASE_TABLE_CONTACT_LIST = "contactList";
	private static final String DATABASE_TABLE_PHONE = "PhoneLogs";
	private static final String DATABASE_TABLE_SMS = "SMSLogs";

	// DEFINE DATABASE VERSION
	private static final int DATABASE_VERSION = 2;

	// DEFINE DATABASE TABLE CREATE QUERY
	private static final String DATABASE_CREATE_TABLEINFO = "create table "
			+ DATABASE_USERINFO
			+ "(Name text not null,Phonenumber text, Gender text, Emailaddress text not null, Age text not null, Job text not null, Imei text not null);";
	// ~DATABASE TOTAL COLUMN
	private static final String DATABASE_CREATE_TABLECONTACT = "create table "
			+ DATABASE_TABLE_CONTACT_LIST
			+ " (_id integer primary key autoincrement, "
			+ "ContactID text not null, Name text not null, Phonenumber text not null, Phonetype text, Emailaddress text, Emailtype text, Address text, Addresstype text, Note text,CompanyName text, Occupationtitle text ,IM text, IMType text, IsStared text, FacebookFriend integer, NumOfCall integer, NumOfSMS integer, HasPhoto integer, HasAnniversary integer, DiffAge integer, Gender text DEFAULT 'man', Seen integer, Family integer, Work integer, Acquaintance integer, Friend integer, Other integer);";
	private static final String DATABASE_CREATE_TABLEPHONE = "create table "
			+ DATABASE_TABLE_PHONE
			+ " (_id integer primary key autoincrement, idCall integer not null,"
			+ "DateTime text not null, ContactID text not null, Duration text not null, CallType text not null);";
	private static final String DATABASE_CREATE_TABLESMS = "create table "
			+ DATABASE_TABLE_SMS
			+ " (_id integer primary key autoincrement, SMSID text not null,"
			+ "DateTime text not null, ThreadID int not null, ContactID text not null, SMSLength int not null, SMSType text not null);";

	// Context
	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	// Constructor
	public DBUserData(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// Log.d(TAG, "Creating or Open Database...");

		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_TABLEINFO);
			db.execSQL(DATABASE_CREATE_TABLECONTACT);
			db.execSQL(DATABASE_CREATE_TABLEPHONE);
			db.execSQL(DATABASE_CREATE_TABLESMS);
			// Log.d(TAG, "Created Tables");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS titles");
			onCreate(db);
		}
	}

	// ---opens the database---
	public DBUserData open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}

	// //////////////////////////////////////////////////////////////
	// //////////////////////////////////////CONTACT TABLE/////////
	// /////////////////////////////////////////////////////////////

	// ---insert new contact logs into the database---
	public long insertInfo(String name, String email, String age, String job,
			String gender, String imei, String phonenum) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_EMAIL, email);
		initialValues.put(KEY_AGE, age);
		initialValues.put(KEY_GENDER, gender);
		initialValues.put(KEY_JOB, job);
		initialValues.put(KEY_IMEI, imei);
		initialValues.put(KEY_PHONENUMBER, phonenum);
		return db.insert(DATABASE_USERINFO, null, initialValues);
	}

	public long updateInfo(String name, String email, String age, String job,
			String gender, String phonenum) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_EMAIL, email);
		initialValues.put(KEY_AGE, age);
		initialValues.put(KEY_JOB, job);
		initialValues.put(KEY_GENDER, gender);
		initialValues.put(KEY_PHONENUMBER, phonenum);
		return db.update(DATABASE_USERINFO, initialValues, null, null);
	}

	public long insertContact(String contactID, String name,
			String phonenumber, String phonetype, String email,
			String emailtype, String address, String addresstype, String note,
			String company, String title, String im, String imtype,
			String isstared, int hasPhoto, int hasAnniversary) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_CONTACTID, contactID);
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_PHONE, phonenumber);
		initialValues.put(KEY_PHONETYPE, phonetype);
		initialValues.put(KEY_EMAIL, email);
		initialValues.put(KEY_EMAILTYPE, emailtype);
		initialValues.put(KEY_ADDRESS, address);
		initialValues.put(KEY_ADDRESSTYPE, addresstype);
		initialValues.put(KEY_NOTE, note);
		initialValues.put(KEY_COMPANY, company);
		initialValues.put(KEY_TITLE, title);
		initialValues.put(KEY_IM, im);
		initialValues.put(KEY_IMTYPE, imtype);
		initialValues.put(KEY_STARED, isstared);
		initialValues.put(KEY_PHOTO, hasPhoto);
		initialValues.put(KEY_ANNIVERSARY, hasAnniversary);
		initialValues.put(KEY_FBFRIEND, 0);
		initialValues.put(KEY_FAMILY, 0);
		initialValues.put(KEY_WORK, 0);
		initialValues.put(KEY_ACQUAINTANCE, 0);
		initialValues.put(KEY_FRIEND, 0);
		initialValues.put(KEY_OTHER, 0);
		initialValues.put(KEY_DIFF_AGE, 0);
		initialValues.put(KEY_NUMBEROFCALL, 0);
		initialValues.put(KEY_NUMBEROFSMS, 0);
		return db.insert(DATABASE_TABLE_CONTACT_LIST, null, initialValues);
	}

	public long insertRelation(String contactID, int family, int work,
			int acquaintance, int friend, int other, int age,
			int facebookFriend, String gender, int seen) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_FBFRIEND, facebookFriend);
		initialValues.put(KEY_FAMILY, family);
		initialValues.put(KEY_WORK, work);
		initialValues.put(KEY_ACQUAINTANCE, acquaintance);
		initialValues.put(KEY_FRIEND, friend);
		initialValues.put(KEY_OTHER, other);
		initialValues.put(KEY_DIFF_AGE, age);
		initialValues.put(KEY_GENDER, gender);
		initialValues.put(KEY_SEEN, seen);

		return db.update(DATABASE_TABLE_CONTACT_LIST, initialValues,
				KEY_CONTACTID + "=" + contactID, null);
	}

	public int hasAtLeastACall(String contactID, int numOfCall) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NUMBEROFCALL, numOfCall);
		return db.update(DATABASE_TABLE_CONTACT_LIST, initialValues,
				KEY_CONTACTID + "=" + contactID, null);
	}

	public int insertNumOfSMS(String contactID, int numOfSMS) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NUMBEROFSMS, numOfSMS);
		return db.update(DATABASE_TABLE_CONTACT_LIST, initialValues,
				KEY_CONTACTID + "=" + contactID, null);
	}

	public Cursor getInfo() {
		return db.query(DATABASE_USERINFO, new String[] { KEY_NAME, KEY_EMAIL,
				KEY_AGE, KEY_JOB, KEY_PHONENUMBER, KEY_GENDER }, null, null,
				null, null, null, null);
	}

	public Cursor getContact() {
		return db.query(DATABASE_TABLE_CONTACT_LIST, new String[] { KEY_ROWID,
				KEY_CONTACTID, KEY_NAME, KEY_PHONE, KEY_PHONETYPE, KEY_EMAIL,
				KEY_EMAILTYPE, KEY_ADDRESS, KEY_ADDRESSTYPE, KEY_NOTE,
				KEY_COMPANY, KEY_TITLE, KEY_IM, KEY_IMTYPE, KEY_STARED,
				KEY_NUMBEROFCALL, KEY_NUMBEROFSMS, KEY_FAMILY, KEY_WORK,
				KEY_ACQUAINTANCE, KEY_FRIEND, KEY_OTHER, KEY_FBFRIEND }, null,
				null, null, null, null, null);
	}

	public Cursor getContact(String id) {
		return db.rawQuery("SELECT * FROM contactList WHERE ContactID = '" + id
				+ "'", null);
	}

	public String getContactIdByPhonenumber(String phonenumber) {
		Cursor cur = db.rawQuery(
				"SELECT ContactID FROM contactList WHERE Phonenumber = '"
						+ phonenumber + "'", null);
		String contactID = null;
		if (cur != null && cur.moveToFirst()) {
			contactID = cur.getString(cur.getColumnIndex("ContactID"));
		}
		cur.close();
		return contactID;
	}

	public void deleteContact() {
		db.delete(DATABASE_TABLE_CONTACT_LIST, null, null);
		// Log.d(TAG, "Deleted ContactList DB");
	}

	// //////////////////////////////////////////////////////////////
	// //////////////////////////////////////PHONE TABLE/////////
	// /////////////////////////////////////////////////////////////
	// ---insert a new call log into the database---
	public long insertCallLogs(int id, String datetime, String contactID,
			String duration, String classification) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_CALLID, id);
		initialValues.put(KEY_DATETIME, datetime);
		initialValues.put(KEY_CONTACTID, contactID);
		initialValues.put(KEY_DURATION, duration);
		initialValues.put(KEY_CALLTYPE, classification);

		return db.insert(DATABASE_TABLE_PHONE, null, initialValues);
	}

	// ---retrieves all the LOGS---
	public Cursor getAllCallLogs() {
		return db.query(DATABASE_TABLE_PHONE, new String[] { KEY_ROWID,
				KEY_CALLID, KEY_DATETIME, KEY_CONTACTID, KEY_DURATION,
				KEY_CALLTYPE }, null, null, null, null, null, null);
	}

	public void deleteCallLog() {
		db.delete(DATABASE_TABLE_PHONE, null, null);
	}

	public Cursor checkPhone(int id) {
		String[] column = new String[] { KEY_CALLID };
		String selection = KEY_CALLID + " = ?";
		String[] selectionArgs = new String[] { String.valueOf(id) };
		return db.query(DATABASE_TABLE_PHONE, column, selection, selectionArgs,
				null, null, null);

	}

	// //////////////////////////////////////////////////////////////
	// //////////////////////////////////////SMS TABLE/////////
	// /////////////////////////////////////////////////////////////

	// ---insert new SMS logs into the database---

	public long insertSMS(int smsId, String datetime, int threadid,
			String contactID, int smslength, String type) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_SMSID, smsId);
		initialValues.put(KEY_DATETIME, datetime);
		initialValues.put(KEY_THREADID, threadid);
		initialValues.put(KEY_CONTACTID, contactID);
		initialValues.put(KEY_SMSLENGTH, smslength);
		initialValues.put(KEY_TYPE, type);

		return db.insert(DATABASE_TABLE_SMS, null, initialValues);
	}

	public Cursor getSMS() {
		return db.query(DATABASE_TABLE_SMS, new String[] { KEY_ROWID,
				KEY_SMSID, KEY_DATETIME, KEY_THREADID, KEY_CONTACTID,
				KEY_SMSLENGTH, KEY_TYPE }, null, null, null, null, null, null);
	}

	public Cursor checkSMS(int smsId) {

		String[] column = new String[] { KEY_SMSID };
		String selection = KEY_SMSID + " = ?";
		String[] selectionArgs = new String[] { String.valueOf(smsId) };
		return db.query(DATABASE_TABLE_SMS, column, selection, selectionArgs,
				null, null, null);
	}

	public void deleteSmsByRowId(int rowId) {
		db.delete(DATABASE_TABLE_SMS, KEY_ROWID + "=" + rowId, null);
	}

	public void deleteSMS() {
		db.delete(DATABASE_TABLE_SMS, null, null);
	}

}
